package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Henrik-iMac
 */


public class DBSupplier implements IFDBSupplier{
    private Connection con;

    public DBSupplier(){
        con = DbConnection.getInstance().getDBcon();
    }
    //get all suppliers
    public ArrayList<Supplier> getAllSuppliers(boolean retrieveAssociation){
        ArrayList suppList = new ArrayList();
        suppList = miscWhere("",retrieveAssociation);
        return suppList;
    }
    //get one supplier
    public Supplier findSupplierById(int id, boolean retrieveAssociation){
        String wClause = "id = " + id;
        System.out.println("Search supplier: " + wClause);
        return singleWhere(wClause, retrieveAssociation);
    }
    public Supplier findSupplierByName(String name, boolean retrieveAssociation){
        String wClause = "name like '%" + name + "%'";
        System.out.println("Search supplier: " + wClause);
        return singleWhere(wClause, retrieveAssociation);
    }

    //insert a new supplier
    public int insertSupplier(Supplier supplier){
        int nextId = GetMax.getMaxId("Select max(id) from Supplier");
        nextId = nextId + 1;
        System.out.println("next id = " +  nextId);

        int rc =-1;

        String query="INSERT INTO Supplier(id, name, street, streetNo, zipCode, country, phoneNo, email) VALUES("+
                nextId + ",'"+
                supplier.getName() + "','"+
                supplier.getStreet() + "',"+
                supplier.getStreetNo() + ","+
                supplier.getZipCode().getPostalCode() + ",'"+
                supplier.getCountry() + "','"+
                supplier.getPhoneNo() + "','"+
                supplier.getEmail() + "')";
        System.out.println("insert: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Insert exception in Supplier db: "+e);
        }
        return rc;
    }
    //update information about a supplier
    public int updateSupplier(Supplier supplier){
        Supplier suppObj = supplier;
        int rc = -1;

        String query = "UPDATE Supplier SET " +
                "id = " + suppObj.getId() + ", " +
                "name ='" + suppObj.getName() + "', "+
                "street ='" + suppObj.getStreet() + "', "+
                "streetNo =" + suppObj.getStreetNo() + ", "+
                //"zipCode =" + suppObj.getZipCode().getPostalCode() + ", "+
                "country ='" + suppObj.getCountry() + "', "+
                "phoneNo ='" + suppObj.getPhoneNo() + "', "+
                "email ='" + suppObj.getEmail() + "' "+
                " WHERE id = " + supplier.getId();
        System.out.println("Update query: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Update exception in Supplier db: "+e);
        }
        return rc;
    }
    //delete supplier
    public int deleteSupplier(int id){
        int rc = -1;
        String query="DELETE FROM Supplier WHERE id = "+id;
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        } 
        catch (Exception e) {
            System.out.println("Delete exception in supplier db: "+e);
        }
        return rc;
    };

    private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Supplier> list = new ArrayList<Supplier>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            int snr = 0;
            while (results.next()){
                Supplier supObj = new Supplier();
                supObj = buildSupplier(results);
                list.add(supObj);
            }
            stmt.close();

            if(retrieveAssociation)
            {
                for(Supplier supObj : list)
                {
                    IFDBPostalCity dbPostObj = new DBPostalCity();
                    PostalCity postObj = dbPostObj.findCityZipCode(supObj.getZipCode().getPostalCode());
                    System.out.println("Zip code is selected");
                    supObj.setZipCode(postObj);
                }
            }
        }
        catch (Exception e) {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return list;
    }

    //Singlewhere is used when we only select one supplier
    private Supplier singleWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        Supplier supObj = new Supplier();
        supObj.setId(0);
        String query = buildQuery(wClause);
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if(results.next()){
                supObj = buildSupplier(results);
                stmt.close();
                if(retrieveAssociation)
                {
                    IFDBPostalCity dbPostObj = new DBPostalCity();
                    PostalCity postObj = dbPostObj.findCityZipCode(supObj.getZipCode().getPostalCode());
                    System.out.println("Zip code is selected");
                    supObj.setZipCode(postObj);
                }
                else
                {
                    supObj.setZipCode(null);
                }
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: "+e);
        }
        return supObj;
    }

    //method to build the query
    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM Supplier";
        if (wClause.length()>0){
            query=query+" WHERE "+ wClause;
        }
        return query;
    }

    private Supplier buildSupplier(ResultSet results){
        Supplier sObj = new Supplier();
        try {
            sObj.setId(results.getInt(1));
            sObj.setName(results.getString(2));
            sObj.setStreet(results.getString(3));
            sObj.setStreetNo(results.getInt(4));
            sObj.setZipCode(new PostalCity(results.getInt(5)));
            sObj.setCountry(results.getString(6));
            sObj.setPhoneNo(results.getString(7));
            sObj.setEmail(results.getString(8));
        }
        catch (Exception e) {
            System.out.println("error in building the supplier object");
        }
        return sObj;
    }
}
